/**
 * Program   :   ExcelUtils.java
 * Author    :   shadow guo
 * Create    :   2009-11-28 下午06:43:25
 *
 * Copyright 2008 by Tongfu Information Technology Co. Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information
 * of Tongfu Information Technology Co. Ltd. ("Confidential Information").  You
 * shall not disclose such Confidential Information and shall use
 * it only in accordance with the terms of the license agreement
 * you entered into with Tongfu Information Technology Co. Ltd.
 * 
 */

/**
 * 
 */
package com.tongfusoft.utils.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.DateUtil;

import com.tongfusoft.exception.ReadExcelException;
import com.tongfusoft.utils.DateUtils;

/**
 * @author shadowguo
 * 
 */
public class ExcelUtils {
	private static Logger logger = Logger.getLogger(ExcelUtils.class);
	public static final String TYPE_STRING = "string";
	public static final String TYPE_NUMERIC = "numeric";
	public static final String TYPE_DATE = "date";

	/**
	 * 
	 * 判断是否是xls文件
	 * 
	 * @param
	 * @return boolean
	 * @throws
	 */
	public static boolean isXLS(File file) {
		String name = file.getName().substring(
				file.getName().lastIndexOf(".") + 1);
		if (name.equalsIgnoreCase("xls")) {
			return true;
		}

		return false;
	}

	/**
	 * 返回sheet中第一行的列数
	 * 
	 * @param
	 * @return int
	 * @throws
	 */
	public static int getColnum(HSSFSheet sheet) throws ReadExcelException {
		try {
			HSSFRow row = sheet.getRow(0);
			return row.getLastCellNum();
		} catch (Exception e) {
			throw new ReadExcelException(e);
		}
	}

	/**
	 * 根据传入的位置和类型，在sheet中填入具体的值
	 * 
	 * @param
	 * @return void
	 * @throws
	 */
	public static void setContent(HSSFSheet sheet, int rownum, int colnum,
			int cType, String value) {
		HSSFRow row = null;
		HSSFCell cell = null;

		row = sheet.getRow(rownum);
		if (row == null) {
			row = sheet.createRow(rownum);
		}

		cell = row.getCell((short) colnum);
		if (cell == null) {
			cell = row.createCell((short) colnum);
		}

		cell.setCellType(cType);
		switch (cType) {
		case HSSFCell.CELL_TYPE_NUMERIC:
			cell.setCellValue(Double.parseDouble(value));
			break;
		case HSSFCell.CELL_TYPE_STRING:
			cell.setCellValue(new HSSFRichTextString(value));
			break;
		case HSSFCell.CELL_TYPE_FORMULA:
			cell.setCellFormula(value);
			break;
		}
	}

	/**
	 * 
	 * 获取指定位置的公式
	 * 
	 * @param
	 * @return String
	 * @throws
	 */
	public static String getFormula(HSSFSheet sheet, int rownum, int colnum)
			throws ReadExcelException {
		HSSFRow row = null;
		HSSFCell cell = null;

		try {
			row = sheet.getRow(rownum);
			if (row != null) {
				cell = row.getCell((short) colnum);

				if (cell != null) {
					if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
						return cell.getCellFormula();
					}
				}
			}

			return "";
		} catch (Exception e) {
			throw new ReadExcelException(e);
		}
	}

	/**
	 * 
	 * 返回指定的单元格
	 * 
	 * @param
	 * @return HSSFCell
	 * @throws
	 */
	public static HSSFCell getCell(HSSFSheet sheet, int rownum, int colnum)
			throws ReadExcelException {
		HSSFRow row = null;
		HSSFCell cell = null;

		try {
			row = sheet.getRow(rownum);
			if (row != null) {
				cell = row.getCell((short) colnum);
				return cell;
			}

			return null;
		} catch (Exception e) {
			throw new ReadExcelException(e);
		}

	}

	/**
	 * 
	 * 获取单元格的内容
	 * 
	 * @parameter sheet
	 * @param rownum
	 * @param column
	 * @return String
	 * @throws
	 */
	public static String getContent(HSSFSheet sheet, int rownum, int colnum)
			throws ReadExcelException {
		HSSFRow row = null;
		HSSFCell cell = null;
		HSSFRichTextString text = null;
		java.text.NumberFormat nf = java.text.NumberFormat.getInstance();
		nf.setGroupingUsed(false);

		try {
			row = sheet.getRow(rownum);
			if (row != null) {
				cell = row.getCell((short) colnum);

				if (cell != null) {
					int cType = cell.getCellType();
					switch (cType) {
					case HSSFCell.CELL_TYPE_NUMERIC:
						if (DateUtil.isCellDateFormatted(cell)) {
							return DateUtils.formatDateTime(DateUtil
									.getJavaDate(cell.getNumericCellValue()));
						} else {
							return nf.format(cell.getNumericCellValue());
						}
					case HSSFCell.CELL_TYPE_STRING:
						text = cell.getRichStringCellValue();
						return text.getString().trim();
					case HSSFCell.CELL_TYPE_FORMULA:
						sheet.setDisplayFormulas(true);
						return nf.format(cell.getNumericCellValue());
					case HSSFCell.CELL_TYPE_BLANK:
						return "";
					}
				}
			}

			return null;
		} catch (Exception e) {
			throw new ReadExcelException(e);
		}
	}

	/**
	 * 
	 * 获取单元格对象
	 * 
	 * @param sheet
	 * @param rownum
	 * @param colnum
	 * @param type
	 * @return Object
	 * @throws
	 */
	public static Object getContent(HSSFSheet sheet, int rownum, int colnum,
			String type) throws ReadExcelException {
		HSSFRow row = null;
		HSSFCell cell = null;
		HSSFRichTextString text = null;
		java.text.NumberFormat nf = java.text.NumberFormat.getInstance();
		nf.setGroupingUsed(false);

		try {
			row = sheet.getRow(rownum);
			if (row != null) {
				cell = row.getCell((short) colnum);

				if (cell != null) {
					int cType = cell.getCellType();
					switch (cType) {
					case HSSFCell.CELL_TYPE_NUMERIC:
						if (type.equals(ExcelUtils.TYPE_DATE)) {
							return cell.getDateCellValue();
						} else {
							return nf.format(cell.getNumericCellValue());
						}
					case HSSFCell.CELL_TYPE_STRING:
						text = cell.getRichStringCellValue();
						return text.getString().trim();
					case HSSFCell.CELL_TYPE_FORMULA:
						sheet.setDisplayFormulas(true);
						return nf.format(cell.getNumericCellValue());
						// return Double.toString(cell.getNumericCellValue());
					case HSSFCell.CELL_TYPE_BLANK:
						if (type.equals(ExcelUtils.TYPE_NUMERIC)) {
							return "0";
						}

						return "";
					}
				} else {
					if (type.equals(ExcelUtils.TYPE_NUMERIC)) {
						return "0";
					} else {
						return "";
					}
				}
			}

			return "";
		} catch (Exception e) {
			throw new ReadExcelException(e);
		}
	}

	/**
	 * 
	 * 获取标题
	 * 
	 * @param sheet
	 * @param startrow
	 * @param startcol
	 * @return List<String>
	 * @throws
	 */
	public static List<String> getTitle(HSSFSheet sheet, int startrow,
			int startcol) throws ReadExcelException {
		List<String> titleList = new ArrayList<String>();
		int colnum = getColnum(sheet);

		for (int i = startcol; i < colnum; i++) {
			titleList.add(ExcelUtils.getContent(sheet, startrow, i));
		}

		return titleList;
	}

	/**
	 * 获取HSSFWorkbook
	 * @param file
	 * @return
	 */
	public static HSSFWorkbook getWorkbook(String file) {
		FileInputStream fis;
		try {
			fis = new FileInputStream(file);
			POIFSFileSystem fs = new POIFSFileSystem(fis);
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			return wb;
		} catch (FileNotFoundException e) {
			throw new ReadExcelException(e);
		} catch (IOException e) {
			throw new ReadExcelException(e);
		}
		
	}

}
